package dao;

import dbutil.DBHelp;
import vo.Grade;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * Created by ssw on 2018/6/16.
 */
public class GradeDao {
    public List<Grade> askGradeList(String uid) {
        DBHelp dh=new DBHelp();
        List<Grade> gradeList=new LinkedList<>();
        String sql="select grade.sid,grade.ggrade,course.cname\n" +
                "from grade,sc,course\n" +
                "where grade.scid=sc.scid and sc.cid=course.cid and grade.sid=?;";
        try {
            ResultSet rs=dh.query(sql,uid);
            while(rs.next()){
                gradeList.add(new Grade(rs.getString(1),rs.getInt(2 ),rs.getString(3)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                dh.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return gradeList;
    }

    public boolean addScore(String scid, String sid, int ggrade) {
        DBHelp dh=new DBHelp();
        String sql="insert into grade(scid,sid,ggrade) values(?,?,?)";
        try {
            dh.insert(sql,scid,sid,ggrade);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                dh.close();
                return true;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public List<Grade> askGradeList_teh(String scid) {
        DBHelp dh=new DBHelp();
        List<Grade> gradeList=new LinkedList<>();
        String sql="select sc.scid,students.sid,users.uname,course.cname,sc.clname,grade.ggrade\n" +
                "from sc,users,course,students left join grade on students.sid=grade.sid\n" +
                "where sc.cid=course.cid and.sc.clname=students.clname and students.sid=users.uid and\n" +
                "sc.scid=?";
        try {
            ResultSet rs=dh.query(sql,scid);
            while(rs.next()){
                if(rs.getString(6)==null)
                    gradeList.add(new Grade(rs.getString(1),rs.getString(2),rs.getString(3),
                            rs.getString(4),rs.getString(5),-100));

                else
                    gradeList.add(new Grade(rs.getString(1),rs.getString(2),rs.getString(3),
                            rs.getString(4),rs.getString(5),rs.getInt(6)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                dh.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return gradeList;
    }
}
